use [family-annal]
GO
-- Drop trigger in table tb_fa_person
IF OBJECT_ID ('tb_fa_master_data.trigger_fa_master_data_insert_update','TR') IS NOT NULL
   DROP TRIGGER tb_fa_master_data.trigger_fa_master_data_insert_update;
GO
-- Drop table and all constraint if exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_fa_master_data]') AND name = N'pk_tb_fa_master_data')
BEGIN
ALTER TABLE [dbo].[tb_fa_master_data] DROP CONSTRAINT [pk_tb_fa_master_data]
END
GO
USE [family-annal]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_fa_master_data]') AND type in (N'U'))
DROP TABLE [dbo].[tb_fa_master_data]
GO
use [family-annal]
GO
-- create table person
create table tb_fa_master_data
(
	master_data_id int identity(1,1) not null,
	master_data_key varchar(200),
	master_data_code varchar(200),
	master_data_type_1 varchar(200),
	master_data_type_2 varchar(200),
	master_data_type_3 float,
	master_data_name nvarchar(300),
	master_data_value_num float,
	master_data_value_datetime datetime,
	master_data_value_string nvarchar(max),
	master_data_description nvarchar(max),
	create_date datetime,
	create_uid int,
	update_date datetime,
	update_uid int,
	master_data_status varchar(1) default 'Y'
);
-- create constraint primary key
alter table tb_fa_master_data
		add constraint pk_tb_fa_master_data primary key (master_data_id);
GO
use [family-annal];
GO
-- create trigger for create_date
CREATE TRIGGER trigger_fa_master_data_insert_update
   ON  tb_fa_master_data 
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS(SELECT * FROM INSERTED)
    BEGIN
		update tb_fa_master_data set create_date = GETDATE(), update_date = GETDATE()
		from tb_fa_master_data t join inserted i
		on t.master_data_id = i.master_data_id;
    END
    IF EXISTS(SELECT * FROM DELETED)
    BEGIN
		update tb_fa_master_data set update_date = GETDATE()
		from tb_fa_master_data t join deleted u
		on t.master_data_id = u.master_data_id;
    END
END
GO